package edu.iiist.web.dbutility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

public class DBTool {
 	
 	private static Connection con = null;   
 	private static String username = "root";
 	private static String password = "iiist";
 	private static String dbDriver = "com.mysql.jdbc.Driver";
 	private static String url = "jdbc:mysql://localhost/iiist";
 	
	private static void getConnect()throws Exception 
	{  
		try{
			
			Class.forName(dbDriver).newInstance();
			con = DriverManager.getConnection(url, username, password);
		}catch( Exception e ) {
			 e.printStackTrace();  
		}
	}
    
	public static int ExecuteNonQuery(String cmdtext, String[] parms)throws Exception
	{
		PreparedStatement pstmt = null;  
		try {
			getConnect();  
			pstmt = con.prepareStatement(cmdtext);  
			prepareCommand(pstmt, parms); 
			return pstmt.executeUpdate();  
		}catch (Exception e){
			e.printStackTrace();
		}finally{
			try{ 
				if (pstmt != null)  
					pstmt.close();  
				if (con != null)  
					con.close();  
			}catch (Exception e){  
				e.getMessage();  
			}  
		}
		return -1;
	}  
       
 	public static ArrayList<Object[]> ExecuteReader(String cmdtext, String[] parms)throws Exception 
 	{  
 		PreparedStatement pstmt = null;  
 		ArrayList<Object[]> al = new ArrayList<Object[]>();  

		try{ 
			getConnect();  
			pstmt = con.prepareStatement(cmdtext);  
			prepareCommand(pstmt, parms);  
			ResultSet rs = pstmt.executeQuery();  
               
			ResultSetMetaData rsmd = rs.getMetaData();  
			int column = rsmd.getColumnCount();  
               
			while (rs.next()) {  
				Object[] ob = new Object[column];  
				for (int i=1; i<=column; i++) 
				{  
					ob[i-1] = rs.getObject(i);  
				} 
				if(ob!=null) 
					al.add(ob);  
			}  
               
			rs.close();  
			return al;  
               
		}catch (Exception e){  
			e.printStackTrace();  
		}finally{  
			try{
				if (pstmt != null)  
					pstmt.close();  
				if (con != null)  
					con.close();  
			}catch (Exception e){  
				e.printStackTrace();  
			}  
		}
		return al;  
 	}  
       
	public static Object ExecuteScalar(String cmdtext, String name,String[] parms) throws Exception 
	{
		PreparedStatement pstmt = null;  
		ResultSet rs = null;  
		try{
			getConnect();  
			pstmt = con.prepareStatement(cmdtext);  
			prepareCommand(pstmt, parms);  
               
			rs = pstmt.executeQuery();  
			if (rs.next()) 
			{  
				return rs.getObject(name);  
			}else 
			{  
				return null;  
			}  
		}catch (Exception e){  
			e.printStackTrace();  
		}finally 
		{  
			try{  
				if (rs != null)  
					rs.close();  
				if (pstmt != null)  
					pstmt.close();  
				if (con != null)  
					con.close();  
			}catch (Exception e) 
 			{  
				e.printStackTrace();  
 			}  
		}
  	return null;  
	}  
      
	private static void prepareCommand(PreparedStatement pstmt, String[] parms)throws Exception 
	{  
		try{
			if (parms != null) 
			{
				for (int i = 0; i < parms.length; i++) 
				{
					try{  
						pstmt.setDate(i + 1, java.sql.Date.valueOf(parms[i]));  
					}catch (Exception e){
						try {  
							pstmt.setDouble(i + 1, Double.parseDouble(parms[i]));  
						}catch (Exception e1){  
							try{  
								pstmt.setInt(i + 1, Integer.parseInt(parms[i]));  
							}catch (Exception e2){  
								try{  
									pstmt.setString(i + 1, parms[i]);  
								}catch (Exception e3){  
									System.out.print("DBTool-PrepareCommand Err1:" + e3);  
								}  
							}  
						}  
					}  
				}
			}  
		}catch (Exception e1){  
			System.out.print("DBTool-PrepareCommand Err2:" + e1);  
		}  
	}  
}
